﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;

namespace TPLOAWebService.Models
{
    public class TPLOADBManager
    {
        #region Clinic
        public static ArrayList GetAllClinic()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Clinic";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Clinic c = new Clinic();
                    c.ClinicID = (string)dr["ClinicID"];
                    c.Name = (string)dr["Name"];
                    c.Address = (string)dr["Address"];
                    c.TelNo = (string)dr["TelNo"];
                    result.Add(c);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static Clinic GetClinicByID(string id)
        {
            Clinic c = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Clinic WHERE ClinicID=@ClinicID";
                comm.Parameters.AddWithValue("@ClinicID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    c = new Clinic();
                    c.ClinicID = (string)dr["ClinicID"];
                    c.Name = (string)dr["Name"];
                    c.Address = (string)dr["Address"];
                    c.TelNo = (string)dr["TelNo"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return c;
        }

        public static int InsertClinic(Clinic c)
        {
            int rowsinserted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Clinic(ClinicID,Name,Address,TelNo) VALUES (@ClinicID,@Name,@Address,@TelNo)";
                comm.Parameters.AddWithValue("@ClinicID", c.ClinicID);
                comm.Parameters.AddWithValue("@Name", c.Name);
                comm.Parameters.AddWithValue("@Address", c.Address);
                comm.Parameters.AddWithValue("@TelNo", c.TelNo);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int UpdateClinic(Clinic c)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Clinic SET Name=@Name,Address=@Address,TelNo=@TelNo WHERE ClinicID=@ClinicID";
                comm.Parameters.AddWithValue("@Name", c.Name);
                comm.Parameters.AddWithValue("@Address", c.Address);
                comm.Parameters.AddWithValue("@TelNo", c.TelNo);
                comm.Parameters.AddWithValue("@ClinicID", c.ClinicID);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int DeleteClinic(string id)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Clinic WHERE ClinicID=@ClinicID";
                comm.Parameters.AddWithValue("@ClinicID", id);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }
        #endregion

        #region Course
        public static ArrayList GetAllCourse()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Course";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Course c = new Course();
                    c.CourseCode = (string)dr["CourseCode"];
                    c.CourseName = (string)dr["CourseName"];
                    result.Add(c);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static Course GetCourseByCourseCode(string cc)
        {
            Course c = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Course WHERE CourseCode=@CourseCode";
                comm.Parameters.AddWithValue("@CourseCode", cc);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    c = new Course();
                    c.CourseCode = (string)dr["CourseCode"];
                    c.CourseName = (string)dr["CourseName"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return c;
        }

        public static int InsertCourse(Course c)
        {
            int rowsinserted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Course(CourseCode,CourseName) VALUES (@CourseCode,@CourseName)";
                comm.Parameters.AddWithValue("@CourseCode", c.CourseCode);
                comm.Parameters.AddWithValue("@CourseName", c.CourseName);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int UpdateCourse(Course c)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Course SET CourseName=@CourseName WHERE CourseCode=@CourseCode";
                comm.Parameters.AddWithValue("@CourseName", c.CourseName);
                comm.Parameters.AddWithValue("@CourseCode", c.CourseCode);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int DeleteCourse(string cc)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Course WHERE CourseCode=@CourseCode";
                comm.Parameters.AddWithValue("@CourseCode", cc);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }
        #endregion

        #region LOA
        public static ArrayList GetAllLOA()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM LOA";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    LOA l = new LOA();
                    l.LOAID = (int)dr["LOAID"];
                    l.Type = (string)dr["Type"];
                    l.SubmitDateTime = Convert.ToString((DateTime)dr["SubmitDateTime"]);
                    l.StartDateTime = Convert.ToString((DateTime)dr["StartDateTime"]);
                    l.EndDateTime = Convert.ToString((DateTime)dr["EndDateTime"]);
                    l.LOAReason = (string)dr["LOAReason"];
                    if (Convert.ToChar((string)dr["GradedAssignmentsClash"]) == 'Y')
                    {
                        l.GradedAssignmentClash = true;
                        l.GradedAssignmentClashDetails = (string)dr["GradedAssignmentsClashDetails"];
                    }
                    else
                    {
                        l.GradedAssignmentClash = false;
                    }
                    l.ReviewStatus = (string)dr["ReviewStatus"];
                    if (DBNull.Value != (object)dr["RejectReason"])
                    {
                        l.RejectReason = (string)dr["RejectReason"];
                    }
                    if (DBNull.Value != (object)dr["ReviewDateTime"])
                    {
                        l.ReviewDateTime = Convert.ToString((DateTime)dr["ReviewDateTime"]);
                    }
                    l.StudentAdminNo = (string)dr["StudentAdminNo"];
                    if (DBNull.Value != (object)dr["MCID"])
                    {
                        l.MCID = (int)dr["MCID"];
                    }
                    result.Add(l);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static LOA GetLOAByID(int id)
        {
            LOA l = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM LOA WHERE LOAID=@LOAID";
                comm.Parameters.AddWithValue("@LOAID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    l = new LOA();
                    l.LOAID = (int)dr["LOAID"];
                    l.Type = (string)dr["Type"];
                    l.SubmitDateTime = Convert.ToString((DateTime)dr["SubmitDateTime"]);
                    l.StartDateTime = Convert.ToString((DateTime)dr["StartDateTime"]);
                    l.EndDateTime = Convert.ToString((DateTime)dr["EndDateTime"]);
                    l.LOAReason = (string)dr["LOAReason"];
                    if (Convert.ToChar((string)dr["GradedAssignmentsClash"]) == 'Y')
                    {
                        l.GradedAssignmentClash = true;
                        l.GradedAssignmentClashDetails = (string)dr["GradedAssignmentsClashDetails"];
                    }
                    else
                    {
                        l.GradedAssignmentClash = false;
                    }
                    l.ReviewStatus = (string)dr["ReviewStatus"];
                    if (DBNull.Value != (object)dr["RejectReason"])
                    {
                        l.RejectReason = (string)dr["RejectReason"];
                    }
                    if (DBNull.Value != (object)dr["ReviewDateTime"])
                    {
                        l.ReviewDateTime = Convert.ToString((DateTime)dr["ReviewDateTime"]);
                    }
                    l.StudentAdminNo = (string)dr["StudentAdminNo"];
                    if (DBNull.Value != (object)dr["MCID"])
                    {
                        l.MCID = (int)dr["MCID"];
                    }
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return l;
        }

        public static ArrayList GetLOAByAdminNo(string id)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM LOA WHERE StudentAdminNo=@StudentAdminNo";
                comm.Parameters.AddWithValue("@StudentAdminNo", id);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    LOA l = new LOA();
                    l.LOAID = (int)dr["LOAID"];
                    l.Type = (string)dr["Type"];
                    l.SubmitDateTime = Convert.ToString((DateTime)dr["SubmitDateTime"]);
                    l.StartDateTime = Convert.ToString((DateTime)dr["StartDateTime"]);
                    l.EndDateTime = Convert.ToString((DateTime)dr["EndDateTime"]);
                    l.LOAReason = (string)dr["LOAReason"];
                    if (Convert.ToChar((string)dr["GradedAssignmentsClash"]) == 'Y')
                    {
                        l.GradedAssignmentClash = true;
                        l.GradedAssignmentClashDetails = (string)dr["GradedAssignmentsClashDetails"];
                    }
                    else
                    {
                        l.GradedAssignmentClash = false;
                    }
                    l.ReviewStatus = (string)dr["ReviewStatus"];
                    if (DBNull.Value != (object)dr["RejectReason"])
                    {
                        l.RejectReason = (string)dr["RejectReason"];
                    }
                    if (DBNull.Value != (object)dr["ReviewDateTime"])
                    {
                        l.ReviewDateTime = Convert.ToString((DateTime)dr["ReviewDateTime"]);
                    }
                    l.StudentAdminNo = (string)dr["StudentAdminNo"];
                    if (DBNull.Value != (object)dr["MCID"])
                    {
                        l.MCID = (int)dr["MCID"];
                    }
                    result.Add(l);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static ArrayList GetLOAByCourseCode(string cc)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM LOA WHERE StudentAdminNo IN (SELECT AdminNo FROM Student WHERE CourseCode=@CourseCode)";
                comm.Parameters.AddWithValue("@CourseCode", cc);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    LOA l = new LOA();
                    l.LOAID = (int)dr["LOAID"];
                    l.Type = (string)dr["Type"];
                    l.SubmitDateTime = Convert.ToString((DateTime)dr["SubmitDateTime"]);
                    l.StartDateTime = Convert.ToString((DateTime)dr["StartDateTime"]);
                    l.EndDateTime = Convert.ToString((DateTime)dr["EndDateTime"]);
                    l.LOAReason = (string)dr["LOAReason"];
                    if (Convert.ToChar((string)dr["GradedAssignmentsClash"]) == 'y')
                    {
                        l.GradedAssignmentClash = true;
                        l.GradedAssignmentClashDetails = (string)dr["GradedAssignmentsClashDetails"];
                    }
                    else
                    {
                        l.GradedAssignmentClash = false;
                    }
                    l.ReviewStatus = (string)dr["ReviewStatus"];
                    if (DBNull.Value != (object)dr["RejectReason"])
                    {
                        l.RejectReason = (string)dr["RejectReason"];
                    }
                    if (DBNull.Value != (object)dr["ReviewDateTime"])
                    {
                        l.ReviewDateTime = Convert.ToString((DateTime)dr["ReviewDateTime"]);
                    }
                    l.StudentAdminNo = (string)dr["StudentAdminNo"];
                    if (DBNull.Value != (object)dr["MCID"])
                    {
                        l.MCID = (int)dr["MCID"];
                    }
                    result.Add(l);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static int InsertLOA(LOA l)
        {
            int rowsinserted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO LOA(Type,SubmitDateTime,StartDateTime,EndDateTime,LOAReason,GradedAssignmentsClash,"
                    + "GradedAssignmentsClashDetails,ReviewStatus,StudentAdminNo,MCID) VALUES (@Type,"
                    + "@SubmitDateTime,@StartDateTime,@EndDateTime,@LOAReason,@GradedAssignmentsClash,@GradedAssignmentsClashDetails,"
                    + "@ReviewStatus,@StudentAdminNo,@MCID)";
                comm.Parameters.AddWithValue("@Type", l.Type);
                comm.Parameters.AddWithValue("@SubmitDateTime", l.SubmitDateTime);
                comm.Parameters.AddWithValue("@StartDateTime", l.StartDateTime);
                comm.Parameters.AddWithValue("@EndDateTime", l.EndDateTime);
                comm.Parameters.AddWithValue("@LOAReason", l.LOAReason);
                if (l.GradedAssignmentClash)
                {
                    comm.Parameters.AddWithValue("@GradedAssignmentsClash", 'Y');
                }
                else
                {
                    comm.Parameters.AddWithValue("@GradedAssignmentsClash", 'N');
                }
                comm.Parameters.AddWithValue("@GradedAssignmentsClashDetails", l.GradedAssignmentClashDetails);
                comm.Parameters.AddWithValue("@ReviewStatus", l.ReviewStatus);
                comm.Parameters.AddWithValue("@StudentAdminNo", l.StudentAdminNo);
                comm.Parameters.AddWithValue("@MCID", l.MCID);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int UpdateLOA(LOA l)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE LOA SET StartDateTime=@StartDateTime,EndDateTime=@EndDateTime,LOAReason=@LOAReason,"
                    + "GradedAssignmentsClash=@GradedAssignmentsClash,GradedAssignmentsClashDetails=@GradedAssignmentsClashDetails,"
                    + "ReviewStatus=@ReviewStatus,RejectReason=@RejectReason,ReviewDateTime=@ReviewDateTime WHERE LOAID=@LOAID";
                comm.Parameters.AddWithValue("@StartDateTime", l.StartDateTime);
                comm.Parameters.AddWithValue("@EndDateTime", l.EndDateTime);
                comm.Parameters.AddWithValue("@LOAReason", l.LOAReason);
                if (l.GradedAssignmentClash)
                {
                    comm.Parameters.AddWithValue("@GradedAssignmentsClash", 'Y');
                }
                else
                {
                    comm.Parameters.AddWithValue("@GradedAssignmentsClash", 'N');
                }
                comm.Parameters.AddWithValue("@GradedAssignmentsClashDetails", l.GradedAssignmentClashDetails);
                comm.Parameters.AddWithValue("@ReviewStatus", l.ReviewStatus);
                comm.Parameters.AddWithValue("@RejectReason", l.RejectReason);
                comm.Parameters.AddWithValue("@ReviewDateTime", l.ReviewDateTime);
                comm.Parameters.AddWithValue("@LOAID", l.LOAID);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int DeleteLOA(int id)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM LOA WHERE LOAID=@LOAID";
                comm.Parameters.AddWithValue("@LOAID", id);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }
        #endregion

        #region MC
        public static ArrayList GetAllMC()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM MC";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    MC m = new MC();
                    m.MCID = (int)dr["MCID"];
                    m.ClinicID = (string)dr["ClinicID"];
                    m.DoctorName = (string)dr["DoctorName"];
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static MC GetMCByID(int id)
        {
            MC m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM MC WHERE MCID=@MCID";
                comm.Parameters.AddWithValue("@MCID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new MC();
                    m.MCID = (int)dr["MCID"];
                    m.ClinicID = (string)dr["ClinicID"];
                    m.DoctorName = (string)dr["DoctorName"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }

        public static int InsertMC(MC m)
        {
            int rowsinserted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO MC(ClinicID,DoctorName) VALUES (@ClinicID,@DoctorName)";
                comm.Parameters.AddWithValue("@ClinicID", m.ClinicID);
                comm.Parameters.AddWithValue("@DoctorName", m.DoctorName);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int UpdateMC(MC m)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE MC SET ClinicID=@ClinicID,DoctorName=@DoctorName WHERE MCID=@MCID";
                comm.Parameters.AddWithValue("@ClinicID", m.ClinicID);
                comm.Parameters.AddWithValue("@DoctorName", m.DoctorName);
                comm.Parameters.AddWithValue("@MCID", m.MCID);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int DeleteMC(int id)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM MC WHERE MCID=@MCID";
                comm.Parameters.AddWithValue("@MCID", id);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static int GetNewMCID()
        {
            int result = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT DISTINCT * FROM MC ORDER BY MCID DESC";

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    result = (int)dr["MCID"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
        #endregion

        #region Staff
        public static ArrayList GetAllStaff()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Staff s = new Staff();
                    s.StaffID = (string)dr["StaffID"];
                    s.Name = (string)dr["Name"];
                    s.Email = (string)dr["Email"];
                    s.CourseCode = (string)dr["CourseCode"];
                    if (Convert.ToChar((string)dr["ApprovePermissions"]) == 'Y')
                    {
                        s.ApprovePermissions = true;
                    }
                    else
                    {
                        s.ApprovePermissions = false;
                    }
                    if (Convert.ToChar((string)dr["AdminRights"]) == 'Y')
                    {
                        s.AdminRights = true;
                    }
                    else
                    {
                        s.AdminRights = false;
                    }
                    result.Add(s);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static Staff GetStaffByID(string id)
        {
            Staff s = null;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff WHERE StaffID=@StaffID";
                comm.Parameters.AddWithValue("@StaffID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    s = new Staff();
                    s.StaffID = (string)dr["StaffID"];
                    s.Name = (string)dr["Name"];
                    s.Email = (string)dr["Email"];
                    s.CourseCode = (string)dr["CourseCode"];
                    if (Convert.ToChar((string)dr["ApprovePermissions"]) == 'Y')
                    {
                        s.ApprovePermissions = true;
                    }
                    else
                    {
                        s.ApprovePermissions = false;
                    }
                    if (Convert.ToChar((string)dr["AdminRights"]) == 'Y')
                    {
                        s.AdminRights = true;
                    }
                    else
                    {
                        s.AdminRights = false;
                    }
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return s;
        }

        public static int InsertStaff(Staff s)
        {
            int rowsinserted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Staff(StaffID,Name,Email,CourseCode,ApprovePermissions,AdminRights) VALUES (@StaffID,"
                    + "@Name,@Email,@CourseCode,@ApprovePermissions,@AdminRights)";
                comm.Parameters.AddWithValue("@StaffID", s.StaffID);
                comm.Parameters.AddWithValue("@Name", s.Name);
                comm.Parameters.AddWithValue("@Email", s.Email);
                comm.Parameters.AddWithValue("@CourseCode", s.CourseCode);
                if (s.ApprovePermissions)
                {
                    comm.Parameters.AddWithValue("@ApprovePermissions", 'Y');
                }
                else
                {
                    comm.Parameters.AddWithValue("@ApprovePermissions", 'N');
                }
                if (s.AdminRights)
                {
                    comm.Parameters.AddWithValue("@AdminRights", 'Y');
                }
                else
                {
                    comm.Parameters.AddWithValue("@AdminRights", 'N');
                }
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int UpdateStaff(Staff s)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Staff SET Name=@Name,Email=@Email,CourseCode=@CourseCode,ApprovePermissions=@ApprovePermissions,"
                    + "AdminRights=@AdminRights WHERE StaffID=@StaffID";
                comm.Parameters.AddWithValue("@Name", s.Name);
                comm.Parameters.AddWithValue("@Email", s.Email);
                comm.Parameters.AddWithValue("@CourseCode", s.CourseCode);
                if (s.ApprovePermissions)
                {
                    comm.Parameters.AddWithValue("@ApprovePermissions", 'Y');
                }
                else
                {
                    comm.Parameters.AddWithValue("@ApprovePermissions", 'N');
                }
                if (s.AdminRights)
                {
                    comm.Parameters.AddWithValue("@AdminRights", 'Y');
                }
                else
                {
                    comm.Parameters.AddWithValue("@AdminRights", 'N');
                }
                comm.Parameters.AddWithValue("@StaffID", s.StaffID);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int DeleteStaff(string id)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Staff WHERE StaffID=@StaffID";
                comm.Parameters.AddWithValue("@StaffID", id);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }
        #endregion

        #region Student
        public static ArrayList GetAllStudent()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Student";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Student s = new Student();
                    s.AdminNo = (string)dr["AdminNo"];
                    s.Name = (string)dr["Name"];
                    s.Email = (string)dr["Email"];
                    s.CourseCode = (string)dr["CourseCode"];
                    s.CareGroup = (string)dr["CareGroup"];
                    s.HomeNo = (string)dr["HomeNo"];
                    s.HandphoneNo = (string)dr["HandphoneNo"];
                    s.Classification = (string)dr["Classification"];
                    result.Add(s);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static Student GetStudentByID(string id)
        {
            Student s = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Student WHERE AdminNo=@AdminNo";
                comm.Parameters.AddWithValue("@AdminNo", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    s = new Student();
                    s.AdminNo = (string)dr["AdminNo"];
                    s.Name = (string)dr["Name"];
                    s.Email = (string)dr["Email"];
                    s.CourseCode = (string)dr["CourseCode"];
                    s.CareGroup = (string)dr["CareGroup"];
                    s.HomeNo = (string)dr["HomeNo"];
                    s.HandphoneNo = (string)dr["HandphoneNo"];
                    s.Classification = (string)dr["Classification"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return s;
        }

        public static int InsertStudent(Student s)
        {
            int rowsinserted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Student(AdminNo,Name,Email,CourseCode,CareGroup,HomeNo,HandphoneNo,Classification)"
                    + " VALUES (@AdminNo,@Name,@Email,@CourseCode,@CareGroup,@HomeNo,@HandphoneNo,@Classification)";
                comm.Parameters.AddWithValue("@AdminNo", s.AdminNo);
                comm.Parameters.AddWithValue("@Name", s.Name);
                comm.Parameters.AddWithValue("@Email", s.Email);
                comm.Parameters.AddWithValue("@CourseCode", s.CourseCode);
                comm.Parameters.AddWithValue("@CareGroup", s.CareGroup);
                comm.Parameters.AddWithValue("@HomeNo", s.HomeNo);
                comm.Parameters.AddWithValue("@HandphoneNo", s.HandphoneNo);
                comm.Parameters.AddWithValue("@Classification", s.Classification);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int UpdateStudent(Student s)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Student SET Name=@Name,Email=@Email,CourseCode=@CourseCode,CareGroup=@CareGroup,"
                    + "HomeNo=@HomeNo,HandphoneNo=@HandphoneNo,Classification=@Classification WHERE AdminNo=@AdminNo";
                comm.Parameters.AddWithValue("@Name", s.Name);
                comm.Parameters.AddWithValue("@Email", s.Email);
                comm.Parameters.AddWithValue("@CourseCode", s.CourseCode);
                comm.Parameters.AddWithValue("@CareGroup", s.CareGroup);
                comm.Parameters.AddWithValue("@HomeNo", s.HomeNo);
                comm.Parameters.AddWithValue("@HandphoneNo", s.HandphoneNo);
                comm.Parameters.AddWithValue("@Classification", s.Classification);
                comm.Parameters.AddWithValue("@AdminNo", s.AdminNo);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int DeleteStudent(string id)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Student WHERE AdminNo=@AdminNo";
                comm.Parameters.AddWithValue("@AdminNo", id);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }
        #endregion

        #region Member
        public static ArrayList GetAllMember()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Member";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Member u = new Member();
                    u.Username = (string)dr["Username"];
                    u.Password = (string)dr["Password"];
                    u.UserType = (string)dr["UserType"];
                    result.Add(u);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static Member GetMemberByID(string id)
        {
            Member u = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Member WHERE Username=@Username";
                comm.Parameters.AddWithValue("@Username", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    u = new Member();
                    u.Username = (string)dr["Username"];
                    u.Password = (string)dr["Password"];
                    u.UserType = (string)dr["UserType"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return u;
        }

        public static int InsertMember(Member u)
        {
            int rowsinserted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Member(Username,Password,UserType) VALUES (@Username,@Password,@UserType)";
                comm.Parameters.AddWithValue("@Username", u.Username);
                comm.Parameters.AddWithValue("@Password", u.Password);
                comm.Parameters.AddWithValue("@UserType", u.UserType);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int UpdateMember(Member u)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Member SET Password=@Password WHERE Username=@Username";
                comm.Parameters.AddWithValue("@Password", u.Password);
                comm.Parameters.AddWithValue("@Username", u.Username);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int DeleteMember(string id)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["TPLOADBConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Member WHERE Username=@Username";
                comm.Parameters.AddWithValue("@Username", id);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }
        #endregion
    }
}